#!/usr/bin/python
# -*- coding: utf-8 -*-

import time
import uuid
import functools
import threading
import logging


engin = None


def next_id(t=None):
    """
    create a unique ID, currentTime + Randomdigiter
    """
    if t is None:
        t = time.time()
    return '%015d%s000' % (int(t * 1000), uuid.uuid4().hex)


def _profiling(start, sql=''):
    """
    sql's exec time
    """
    t = time.time() - start
    if t > 0.1:
        logging.warning('[PROFILING] [DB] %s: %s' % (t, sql))
    else:
        logging.info('[PROFILING] [DB] %s: %s' % (t, sql))


def create_engine(user, password, database, host='127.0.0.1', port=3306, **kw):
    """
    The nuclear function, use connect database, creat global object engine,
    database connect belong to engine object
    """
    import mysql.connector
    global engine
    if engine is not None:
        raise DBError('Engine is already initialized.')
    params = dict(user=user, password=password,
                  database=database, host=host, port=port)
    defaults = dict(use_unicode=True, charset='utf-8',
                    collation='utf8_general_ci', autocommit=False)
    for k, v in defaults.iteritems():
        params[k] = kw.pop(k, v)

    params.update(kw)
    params['buffered'] = True
    engine = _Engine(lambda: mysql.connector.connect(**params))
    logging.info('Init mysql engine <%s> ok.' % hex(id(engine)))


def connection():
    """
    The nuclear function for module db. to acquire a connection of database.
    通过_ConnectionCtx 对 _db_ctx封装，使得惰性连接可以自动获取和释放，也就是
    可以使用with 语法来处理数据库连接。
    """
    return _ConnectionCtx()


def with_connection(func):
    """
    设计一个装饰器，替换with语法
    """
    @functools.wraps(func)
    def _wrapper(*args, **kw):
        with _ConnectionCtx():
            return func(*args, **kw)
    return _wrapper


def transaction():
    """
    实现事务功能
    """
    return _TransactionCtx()


def with_transaction(func):
    """
    设计装饰器，替换with语法
    """
    @functools.wraps(func)
    def _wrapper(*args, **kw):
        start=time.time()
        with _TransactionCtx():
            func(*args, **kw)
        _profiling(start)
    return _wrapper


@with_connection
def _seclect(sql, first, *args):
    """
    exec SQL, return one or more result to compose a list
    """
    global _db_ctx
    cursor=None
    sql=sql.replace('?', '%s')
    logging.info('SQL: %s, ARGS: %s' % (sql, args))
    try:
        cursor=_db_ctx.connection.cursor()
        cursor.execute(sql, args)
        if cursor.description:
            names=[x[0] for x in cursor.description]
        if first:
            values=cursor.fetchone()
            if not values:
                return None
            return Dict(names, values)
        return [Dict(names, x) for x in cursor.fetchone()]
    finally:
        if cursor:
            cursor.close()


def select_one(sql, *args):
    return _seclect(sql, True, *args)

def select_int(sql, *args):
    d=_seclect(sql, True, *args)
    if len(d) != 1:
        raise MultiColumnsError('Expect only one column.')
    return d.values()[0]


def select(sql, *args):
    return _seclect(sql, False, *args)


@with_connection
def _update(sql, *args):
    global _db_ctx
    cursor=None
    sql=sql.replace('?', '%s')
    logging.info('SQL: %s, ARGS: %s' % (sql, args))
    try:
        cursor=_db_ctx.connection.cursor()
        cursor.execute(sql, args)
        r=cursor.rowcount
        if _db_ctx.transactions == 0:
            logging.info('auto commit')
            _db_ctx.connection.commit()
        return r
    finally:
        if cursor:
            cursor.close()


def update(sql, *args):
    return _update(sql, *args)


def insert(table, **kw):
    cols, args=zip(*kw.iteritems())
    sql='insert into `%s` (%s) values (%s)' % (table, ','.join(
        ['`%s`' % col for col in cols]), ','.join(['?' for i in range(len(cols))]))
    return _update(sql, *args)


class Dict(dict):
    def __init__(self, names=(), values=(), **kw):
        super(Dict, self).__init__(**kw)
        for k, v in zip(names, values):
            self[k]=v

    def __getattr__(self, key):
        try:
            return self[key]
        except KeyError:
            raise AttributeError(r"'Dict' object has no attribute '%s'" % key)


class DBError(Exception):
    pass

class MultiColumnsError(DBError):
    pass


class _Engine(object):
    def __init__(self, connect):
        self.connect=connect

    def connect(self):
        return self._connect()


class _LazyConnection(object):
    def __init__(self):
        self.connection=None

    def cursor(self):
        if self.connection is None:
            _connection=engine.connect()
            logging.info(
                '[CONNECTION] [OPEN] connection <%s>...' % hex(id(_connection)))
            self.connection=_connection
        return self.connection.cursor()

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    def cleanup(self):
        if self.connection:
            _connection=self.connection
            self.connection=None
            logging.info(
                '[CONNECTION] [CLOSE] connection <%s> ...' % hex(id(connection)))
            _connection.close()


class _DbCtx(threading.local):
    def __init__(self):
        self.connection=None
        self.transactions=0

    def is_init(self):
        return self.connection is not None

    def init(self):
        logging.info('open lazy connection...')
        self.connection=_LazyConnection()
        self.transactions=0

    def cleanup(self):
        self.connection.cleanup()
        self.connection=None

    def cursor(self):
        return self.connection.cursor()

_db_ctx=_DbCtx()


class _ConnectionCtx(object):
    def __enter__(self):
        global _db_ctx
        self.should_cleanup=False
        if not _db_ctx.is_init():
            _db_ctx.init()
            self.should_cleanup=True
        return self

    def __exit__(self, exctype, excvalue, traceback):
        global _db_ctx
        if self.should_cleanup:
            _db_ctx.cleanup()


class _TransactionCtx(object):
    def __enter__(self):
        global _db_ctx
        self.should_close_conn=False
        if not _db_ctx.is_init():
            _db_ctx.init()
            self.should_close_conn=True
        _db_ctx.transactions += 1
        logging.info('begin transaction... ' if _db_ctx.transactions ==
                     1 else 'join current transactions...')
        return self

    def _exit__(self, exctype, excvalue, traceback):
        global _db_ctx
        _db_ctx.transactions -= 1
        try:
            if _db_ctx.transactions == 0:
                if exctype is None:
                    self.commit()
                else:
                    self.rollback()
        finally:
            if self.should_close_conn:
                _db_ctx.cleanup()

    def commit(self):
        global _db_ctx
        logging.info('commit transaction...')
        try:
            _db_ctx.connection.commit()
            logging.info('commit ok.')
        except:
            logging.warning('commit failed . try rollback...')
            _db_ctx.connection.rollback()
            logging.warning('rollback ok')
            raise

    def rollback(self):
        global _db_ctx
        logging.warning('rollback transactions...')
        _db_ctx.connection.rollback()
        logging.info('rollback ok')


if __name__ == '__main':
    logging.basicConfig(level=logging.DEBUG)
    create_engine('root', 'root', 'test', '192.168.1.170')
    update('drop table if exists user')
    update(
        'create table user (id int primary key, name text, email text, passwd text, last_modied real')
    import doctest
    doctest.testmod()





